import json

from xjlibrary.database_moudel.simple.mysqlclient import MySqlDbConnect, SelctSqlFromDB, ExeSqlMany, ExeSqlList, \
    ExeSqlToDB
from xjlibrary.mprocesspoll.MThreadingRun import MThreadingRun
from xjlibrary.mrequest import baserequest
from xjlibrary.msqlite3.connsqlite3 import get_sqlite3_connect
from xjlibrary.msqlite3.sqlite3_client import SelectFromSqllite, ExeSqlliteList
from xjlibrary.myredis.myredisclient import getDataFromRedis
from xjlibrary.our_file_dir.base_dir import BaseDir
from xjlibrary.our_file_dir.image_file import ImageFile

curPath = BaseDir.get_file_dir_absolute(__file__)
TopPath = BaseDir.get_upper_dir(curPath, -2)
sPath = BaseDir.get_new_path(TopPath, "download", "sipopatent", "download", "cover")

listToMysql = []


def Mardb3Conn(db3path):
    return get_sqlite3_connect(db3path)


def MajorDbConnect():
    return MySqlDbConnect(curPath, 'db.ini')


def Selectsql(conn, sql):
    rows = SelectFromSqllite(sql, conn)
    return rows


def SelectsqlMysql(conn, sql):
    rows = SelctSqlFromDB(sql, conn)
    return rows


def execute_many(sql, para):
    ExeSqlMany(sql, para, conn=MajorDbConnect())


def execute_sql_list(listsql, errExit=False):
    ExeSqlList(listsql, MajorDbConnect(), errExit=errExit)


def execute_sql_list_to_db3(listsql, errExit=False):
    ExeSqlliteList(listsql, Mardb3Conn(), errExit=errExit)


def get_url(url, listproxy, endstring=""):
    BoolResult, errString, r = baserequest.MProxyRequest(url, endstring=endstring, proxyRingList=listproxy,
                                                         timeout=(30, 60))
    if not BoolResult:
        return None
    return r


def downandsavecover(cover, filepath, listproxy):
    r = get_url(cover, listproxy)
    if r:
        result_bool = ImageFile.save_img_to_jpg(r.content, filepath)
        return result_bool
    else:
        return False


def step1_db3_to_mysql(dirpath):
    for db3path in BaseDir.get_dir_all_files(dirpath):
        print(db3path)
        sql = "select identifier_pissn,cover from modify_title_info_zt"
        sql2 = "insert ignore into cover(identifier_pissn,coverurl) values(%s,%s)"
        rows = Selectsql(Mardb3Conn(db3path), sql)
        for identifier_pissn, cover in rows:
            listToMysql.append((identifier_pissn, cover))
            if len(listToMysql) > 10000:
                execute_many(sql2, listToMysql)
                listToMysql.clear()

        if len(listToMysql) > 0:
            execute_many(sql2, listToMysql)
            listToMysql.clear()


def step2_downcover_and_update_run(result_queue, listproxy, identifier_pissn, coverurl):
    if coverurl == "images/cp_noimg.jpg" or coverurl == '':
        sql = "update cover set coveruri='',stat=1 where identifier_pissn='{}'".format(identifier_pissn)
        result_queue.put(sql)
    else:
        strdir = identifier_pissn[:-4]
        dirpath = BaseDir.get_new_path(sPath, strdir)
        if not BaseDir.is_dir_exists(dirpath):
            BaseDir.create_dir(dirpath)
        filepath = BaseDir.get_new_path(dirpath, identifier_pissn + ".jpg")
        if not BaseDir.is_file_exists(filepath):
            url = "http://epub.sipo.gov.cn/" + coverurl
            result_bool = downandsavecover(url, filepath, listproxy)
            if result_bool:
                sqluristr = "/smartlib/sipopatent/" + strdir + "/" + str(identifier_pissn) + ".jpg"
                sql = "update cover set coveruri='{}',stat=1 where identifier_pissn='{}'".format(sqluristr,
                                                                                                 identifier_pissn)
                result_queue.put(sql)
            else:
                sql = "update cover set coveruri='',stat=0,failcount = failcount+1 where identifier_pissn='{}'".format(
                    identifier_pissn)
                result_queue.put(sql)
        else:
            sqluristr = "/smartlib/sipopatent/" + strdir + "/" + str(identifier_pissn) + ".jpg"
            sql = "update cover set coveruri='{}',stat=1 where identifier_pissn='{}'".format(sqluristr,
                                                                                             identifier_pissn)
            result_queue.put(sql)


def InsertIntoDbFromList(listSql):
    execute_sql_list(listSql)


def readDBProxy():
    return getDataFromRedis(curPath, 'db.ini')


class DownPageThreadRun(MThreadingRun):
    def __init__(self, num, func):
        super(DownPageThreadRun, self).__init__(num, func)

    def setTask(self, *args, **kwargs):
        sql = "select identifier_pissn,coverurl from cover where stat=0 and failcount<3 ORDER BY `failcount` DESC limit 5000 "
        rows = Selectsql(MajorDbConnect(), sql)
        listid = []
        for identifier_pissn, coverurl in rows:
            listid.append(identifier_pissn)
        if len(listid) == 1:
            listid.append("test")
        sql = "update cover set stat=-1 where  `identifier_pissn` in {}".format(tuple(listid))
        ExeSqlToDB(sql, MajorDbConnect())
        for identifier_pissn, coverurl in rows:
            # 将每一页加入任务队列
            self.add_job(self.func, self.list_proxy, identifier_pissn, coverurl)
        # # 开始线程
        # self.thread_pool.start()

    def dealresult(self, *args, **kwargs):
        InsertIntoDbFromList(self.results)

    def setProxy(self, proxysList=None):
        rows = readDBProxy()
        MThreadingRun.setProxy(self, list(rows))


list_db3_id = []
db3_list = []
jsondict = {}


def Step3_sql_to_db3(dirpath):
    global list_db3_id, db3_list, jsonobj
    # 传入db3目录路径
    for db3path in BaseDir.get_dir_all_files(dirpath):
        list_db3_id.clear()
        # 获取db3的id
        sql = "select identifier_pissn from modify_title_info_zt"
        rows = Selectsql(Mardb3Conn(db3path), sql)
        db3_len = len(rows)
        # 放入list
        for row in rows:
            list_db3_id.append(row[0])
        # 获取 coveruri
        sql = "select identifier_pissn,coveruri from cover where identifier_pissn in {}".format(tuple(list_db3_id))
        rows_mysql = SelectsqlMysql(MajorDbConnect(), sql)
        mysql_len = len(rows_mysql)
        if db3_len != mysql_len:
            print("db3_len is: %s " % str(db3_len))
            print("mysql_len is: %s " % str(mysql_len))
            raise Exception("两个数据库的数据不匹配,请检查")
        # 写入文件 每行是一个字典
        for identifier_pissn, coveruri in rows_mysql:
            assert coveruri is not None and coveruri != "null",Exception("检查数据库是否存在-1的情况")
            jsondict["identifier_pissn"] = identifier_pissn
            jsondict["coveruri"] = coveruri
            with open("jsoncoveruri.big_json", mode='a', encoding='utf-8') as f:
                line = json.dumps(jsondict, ensure_ascii=False).strip() + '\n'
                f.write(line)
        # sys.exit(-1)
        # for identifier_pissn, coveruri in rows_mysql:
        #     sql_update = "update modify_title_info_zt set cover='{}' where identifier_pissn='{}'".format(coveruri,
        #                                                                                                  identifier_pissn)
        #     db3_list.append(sql_update)
        #     if len(db3_list) > 1000:
        #         execute_sql_list_to_db3(db3_list, errExit=True)
        #         db3_list.clear()
        # if len(db3_list) > 0:
        #     execute_sql_list_to_db3(db3_list, errExit=True)
        #     db3_list.clear()


def step2():
    func = step2_downcover_and_update_run
    threadrun = DownPageThreadRun(50, func)
    threadrun.run()


if __name__ == "__main__":
    # var = argv[1]
    # {
    #     "1": step1_db3_to_mysql("./db3"),
    #     "2": step2(),
    #     "3": Step3_sql_to_db3("./db3")
    # }.get(var)
    Step3_sql_to_db3("./db3")
# 第一步 将db3中lngid 和 url地址写入mysql数据库
# 第二步
# 第三步
# Step3_sql_to_db3("./db3")

# 意外的步骤  通过json更新数据库
# list_temp = []
# for line in BaseDir.read_line("jsoncoveruri.big_json"):
#     obj = json.loads(line)
#     sql = "update cover set coveruri='{}',stat=1 where identifier_pissn='{}'".format(obj["coveruri"],
#                                                                                      obj["identifier_pissn"])
#     list_temp.append(sql)
#     if len(list_temp) > 1000:
#         execute_sql_list(list_temp)
#         list_temp.clear()
# if len(list_temp) > 0:
#     execute_sql_list(list_temp)
#     list_temp.clear()
